const { Product, Circle, Tag, VA, Series, sequelize } = require('../models');

class ProductService {

    constructor() {

    }

    async insertProductData(productData, result) {
        const transaction = await sequelize.transaction();

        try {
            // 1. 创建或获取Circle
            const [circle] = await Circle.findOrCreate({
                where: { id: productData.circle.id },
                defaults: { name: productData.circle.name },
                transaction
            });

            // 2. 创建或获取Series
            const [series] = productData.series ? await Series.findOrCreate({
                where: { id: productData.series.id },
                defaults: { name: productData.series.name },
                transaction
            }) : [null];

            // 3. 创建所有VAs
            const vas = await Promise.all(
                productData.va.map(vaName =>
                    VA.findOrCreate({
                        where: { name: vaName },
                        transaction
                    }).then(([va]) => va)
                )
            );

            // 4. 创建或获取所有Tags
            const tags = await Promise.all(
                productData.tags.map(tag =>
                    Tag.findOrCreate({
                        where: { id: tag.id },
                        defaults: { name: tag.name },
                        transaction
                    }).then(([tag]) => tag)
                )
            );

            // 5. 创建Product
            const product = await Product.create({
                rjCode: productData.code,
                title: productData.title,
                circleID: circle?.id || null,
                seriesID: series?.id || null,
                releaseDate: productData.releaseDate,
                ageCategory: productData.ageCategory,
                workType: productData.workType,
                price: productData.price,
                sales: productData.sales.total_sale,
                favorites: parseInt(productData.sales.favorites),
                saleDetail: JSON.parse(productData.sales.sale_detail),
                rating: productData.rating.average_rating,
                ratingCount: productData.rating.rating_count,
                ratingDetail: JSON.parse(productData.rating.score_detail),
                filePath: result.path,
                lang: result.lang,
                isSub: result.isSub,
                isVR: result.isVR,
            }, { transaction });

            // 6. 建立与VA的关联
            await product.addVas(vas, { transaction });

            // 7. 建立与Tags的关联
            await product.addTags(tags, { transaction });

            await transaction.commit();
            console.log('数据插入成功！');

            return product;
        } catch (error) {
            await transaction.rollback();
            console.error('插入数据时发生错误：', error);
            throw error;
        }
    }

    async getAllProducts({
        current = 1,
        pageSize = 15,
        orderBy = 'createdAt',
        order = 'DESC',
        filter = {}
    } = {}) {
        try {
            // 1. 构建查询选项
            const offset = (current - 1) * pageSize;
            const where = {};

            // 添加过滤条件
            if (filter.ageCategory) where.ageCategory = filter.ageCategory;
            if (filter.workType) where.workType = filter.workType;
            if (filter.minPrice) where.price = { [Op.gte]: filter.minPrice };
            if (filter.maxPrice) where.price = { ...where.price, [Op.lte]: filter.maxPrice };
            if (filter.releaseDate) where.releaseDate = filter.releaseDate;

            // 2. 执行查询
            const { count, rows: products } = await Product.findAndCountAll({
                where,
                include: [
                    {
                        model: Circle,
                        as: 'circle',
                        attributes: ['id', 'name']
                    },
                    {
                        model: Series,
                        as: 'series',
                        attributes: ['id', 'name']
                    },
                    {
                        model: VA,
                        as: 'vas',
                        attributes: ['name'],
                        through: { attributes: [] }
                    },
                    {
                        model: Tag,
                        as: 'tags',
                        attributes: ['id', 'name'],
                        through: { attributes: [] }
                    }
                ],
                order: [[orderBy, order]],
                limit: pageSize,
                offset,
                distinct: true // 确保count正确
            });

            // 3. 格式化结果
            const formattedProducts = products.map(product => ({
                rjCode: product.rjCode,
                title: product.title,
                circle: product.circle ? product.circle.name : null,
                // releaseDate: product.releaseDate,
                series: product.series ? product.series.name : null,
                vas: product.vas.map(va => va.name),
                ageCategory: product.ageCategory,
                // workType: product.workType,
                tags: product.tags.map(tag => tag.name),
                price: product.price,
                // sales: product.sales,
                rating: product.rating,
                isVR: product.isVR,
                lang: product.lang || null,
                isSub: product.isSub
            }));

            // 4. 返回结果，包含分页信息
            return {
                products: formattedProducts,
                total: count,
            };
        } catch (error) {
            console.error('Error fetching products:', error);
            throw error;
        }
    }

    // 高级搜索函数
    async searchProducts({
        query = '',
        tags = [],
        circles = [],
        vas = [],
        page = 1,
        limit = 10,
        orderBy = 'releaseDate',
        order = 'DESC'
    } = {}) {
        try {
            const where = {};
            const include = [
                {
                    model: Circle,
                    as: 'circle',
                    attributes: ['id', 'name'],
                    where: circles.length ? { id: circles } : undefined
                },
                {
                    model: Series,
                    as: 'series',
                    attributes: ['id', 'name']
                },
                {
                    model: VA,
                    as: 'vas',
                    attributes: ['name'],
                    through: { attributes: [] },
                    where: vas.length ? { name: vas } : undefined
                },
                {
                    model: Tag,
                    as: 'tags',
                    attributes: ['id', 'name'],
                    through: { attributes: [] },
                    where: tags.length ? { id: tags } : undefined
                }
            ];

            // 添加标题搜索
            if (query) {
                where[Op.or] = [
                    { title: { [Op.like]: `%${query}%` } },
                    { translatedTitle: { [Op.like]: `%${query}%` } }
                ];
            }

            const { count, rows: products } = await Product.findAndCountAll({
                where,
                include,
                order: [[orderBy, order]],
                limit,
                offset: (page - 1) * limit,
                distinct: true
            });

            const formattedProducts = products.map(product => ({
                code: product.rjCode,
                title: product.title,
                circle: product.circle ? {
                    id: product.circle.id,
                    name: product.circle.name
                } : null,
                releaseDate: product.releaseDate,
                series: product.series ? {
                    id: product.series.id,
                    name: product.series.name
                } : null,
                va: product.vas.map(va => va.name),
                ageCategory: product.ageCategory,
                workType: product.workType,
                tags: product.tags.map(tag => ({
                    id: tag.id,
                    name: tag.name
                })),
                price: product.price,
                sales: {
                    total_sale: product.totalSale,
                    favorites: product.favorites.toString(),
                    sale_detail: JSON.stringify(product.saleDetail)
                },
                rating: {
                    average_rating: product.averageRating,
                    rating_count: product.ratingCount,
                    score_detail: JSON.stringify(product.scoreDetail)
                }
            }));

            return {
                products: formattedProducts,
                pagination: {
                    total: count,
                    page,
                    limit,
                    totalPages: Math.ceil(count / limit)
                }
            };
        } catch (error) {
            console.error('Error searching products:', error);
            throw error;
        }
    }



}

module.exports = ProductService;

// // 使用示例
// const examples = async () => {
//     // 1. 基础查询（默认分页）
//     const basicResult = await getAllProducts();

//     // 2. 带筛选的查询
//     const filteredResult = await getAllProducts({
//         page: 1,
//         limit: 20,
//         filter: {
//             ageCategory: '全年龄',
//             minPrice: 1000,
//             maxPrice: 2000
//         },
//         orderBy: 'price',
//         order: 'ASC'
//     });

//     // 3. 搜索特定标签和社团的作品
//     const searchResult = await searchProducts({
//         query: 'ASMR',
//         tags: [497], // ASMR标签
//         circles: [64308], // アトリエメール
//         page: 1,
//         limit: 15
//     });

//     // 4. 获取特定声优的作品
//     const vaWorks = await searchProducts({
//         vas: ['原田彩楓'],
//         orderBy: 'releaseDate',
//         order: 'DESC'
//     });
// };